package com.gameplatform.service;

import com.gameplatform.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


//数据分析的Service
public class AnalyticsService {

    public Map<String, Object> getDashboardData() {
        Map<String, Object> data = new HashMap<>();
        Connection conn = null;

        try {
            conn = DBUtil.getConnection();

            // 获取评分最高的游戏
            data.put("topRatedGames", getTopRatedGames(conn));

            // 获取最受欢迎的游戏（点赞最多）
            data.put("mostLikedGames", getMostLikedGames(conn));

            // 获取评论最多的游戏
            data.put("mostCommentedGames", getMostCommentedGames(conn));

            // 获取收藏最多的游戏
            data.put("mostFavoritedGames", getMostFavoritedGames(conn));

            // 获取游戏分类统计
            data.put("categoryStats", getCategoryStats(conn));

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
        }

        return data;
    }

    private List<Map<String, Object>> getTopRatedGames(Connection conn) throws SQLException {
        List<Map<String, Object>> games = new ArrayList<>();
        String sql = "SELECT title, average_rating, rating_count FROM games WHERE rating_count > 0 ORDER BY average_rating DESC LIMIT 10";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Map<String, Object> game = new HashMap<>();
            game.put("title", rs.getString("title"));
            game.put("averageRating", rs.getDouble("average_rating"));
            game.put("ratingCount", rs.getInt("rating_count"));
            games.add(game);
        }

        return games;
    }

    private List<Map<String, Object>> getMostLikedGames(Connection conn) throws SQLException {
        List<Map<String, Object>> games = new ArrayList<>();
        String sql = "SELECT title, like_count FROM games ORDER BY like_count DESC LIMIT 10";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Map<String, Object> game = new HashMap<>();
            game.put("title", rs.getString("title"));
            game.put("likeCount", rs.getInt("like_count"));
            games.add(game);
        }

        return games;
    }

    private List<Map<String, Object>> getMostCommentedGames(Connection conn) throws SQLException {
        List<Map<String, Object>> games = new ArrayList<>();
        String sql = "SELECT g.title, COUNT(c.id) as comment_count FROM games g LEFT JOIN comments c ON g.id = c.game_id GROUP BY g.id, g.title ORDER BY comment_count DESC LIMIT 10";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Map<String, Object> game = new HashMap<>();
            game.put("title", rs.getString("title"));
            game.put("commentCount", rs.getInt("comment_count"));
            games.add(game);
        }

        return games;
    }

    private List<Map<String, Object>> getMostFavoritedGames(Connection conn) throws SQLException {
        List<Map<String, Object>> games = new ArrayList<>();
        String sql = "SELECT title, favorite_count FROM games ORDER BY favorite_count DESC LIMIT 10";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Map<String, Object> game = new HashMap<>();
            game.put("title", rs.getString("title"));
            game.put("favoriteCount", rs.getInt("favorite_count"));
            games.add(game);
        }

        return games;
    }

    private List<Map<String, Object>> getCategoryStats(Connection conn) throws SQLException {
        List<Map<String, Object>> stats = new ArrayList<>();
        String sql = "SELECT category, COUNT(*) as count FROM games GROUP BY category ORDER BY count DESC";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Map<String, Object> stat = new HashMap<>();
            stat.put("category", rs.getString("category"));
            stat.put("count", rs.getInt("count"));
            stats.add(stat);
        }

        return stats;
    }
}